<?php

namespace app\order\logic;

use app\data\util\HttpUtil;
use app\data\util\MqQueueName;
use app\systems\logic\MqTaskPushLogic;
use think\facade\Db;
use think\facade\Cache;
use util\Excel;
use think\exception\ValidateException;
use think\facade\Log;
use util\Token;
use util\Upload;
use think\cache\driver\Redis;
use app\api\logic\ApiAdvLogic;

class OrderSourceWoobillyLogic
{
    public function __construct()
    {
    }

    /**
     * @param $params
     * @return \think\response\Json
     * @throws \think\db\exception\DbException
     * @desc 列表
     * @author zhangheng
     */
    public function index($params = [])
    {
        $limit = $params['limit'] ?? 10;
        $page  = $params['page'] ?? 1;

        $where = $this->buildTableParames($params);
        $where[]  = ['financial_status', 'in', ["partially_paid", "paid", "partially_refunded", "refunded"]];

        $order = ['createtime' => 'desc'];
        $lists = Db::name('order_source')
            ->field('id,name,total_price,financial_status,created_at,status,firstVisit_source,firstVisit_campaign,lastVisit_source,lastVisit_campaign,first_source,first_campaign,last_source,last_campaign,tag_source,tag_campaign,landing_site,tags,skus')
            ->where($where)
            ->order($order)
            ->limit(($page - 1) * $limit, $limit)
            ->select();

        $lists = objectToArray($lists);
        $count =  Db::name('order_source')->where($where)->count();

        $data = [
            'list'  => $lists,
            'total' => $count,
        ];
        return json(DefaultResponse(200, true, '获取成功', $data));
    }
    /**
     * @param $params
     * @return \think\response\Json
     * @throws \think\db\exception\DbException
     * @desc 运营标签列表
     * @author zhangheng
     */
    public function OperationLabelList($params = [])
    {
        $data = $this->arr_campaign();
        return json(DefaultResponse(200, true, '获取成功', $data));
    }
    /**
     * @param $params
     * @return \think\response\Json
     * @throws \think\db\exception\DbException
     * @desc 渠道标签列表
     * @author zhangheng
     */
    public function ChannelLabelList($params = [])
    {
        $data = $this->arr_source();
        return json(DefaultResponse(200, true, '获取成功', $data));
    }

    /**
     * @param $params
     * @return \think\response\Json
     * @throws \think\db\exception\DbException
     * @desc 导出
     * @author zhangheng
     */
    public function export($params = [])
    {
        $header = [];
        $header[] = ['日期', 'created_at'];
        $header[] = ['订单号', 'name'];
        $header[] = ['支付状态', 'financial_status'];
        $header[] = ['物流状态', 'fulfillment_status'];
        $header[] = ['现销售额$', 'total_price'];
        $header[] = ['币种', 'currency'];
        $header[] = ['退款', 'refund'];
        $header[] = ['物流成本$', 'freight'];
        $header[] = ['采购成本$', 'material'];
        $header[] = ['争议成本$', 'dispute'];
        $header[] = ['美金汇率', 'dollar'];
        $header[] = ['欧元汇率', 'euro_exchange_rate'];
        $header[] = ['tag_source', 'tag_source'];
        $header[] = ['tag_campaign', 'tag_campaign'];
        $header[] = ['原记录标签', 'tags'];
        $header[] = ['landing_site', 'landing_site'];

        $header[] = ['物流匹配状态', 'freight_status'];
        $header[] = ['采购匹配状态', 'material_status'];
        $header[] = ['争议类型', 'disputeapi_type'];
        $header[] = ['争议状态', 'disputeapi_status'];
        $header[] = ['SKU', 'sku'];
        $header[] = ['总件数', 'skuquantity'];

        $tableName = "OrderSource";

        // $where = $this->buildTableParames($params);
        $createtime = $params['createtime'] ?? "";
        $shop_id = $params['shop_id'] ?? "";
        if (!empty($shop_id)) {
            $where[] = ['a.shop_id', '=', $shop_id];
        }
        if (!empty($createtime)) {
            $where[] = ['a.createtime', '>=', strtotime($createtime['0'])];
            $where[] = ['a.createtime', '<=', strtotime($createtime['1']) + 3600*24-1];

            //汇率
            $date = date('Y-m', strtotime($createtime['0']));
        }else{
            // $yesterday = strtotime(date('Y-m-d', strtotime('-1 day') - timezone())); 
            // $where[] = ['a.createtime', '>=', $yesterday];
            // $where[] = ['a.createtime', '<=', $yesterday + 3600*24-1];

            //汇率
            $date = date('Y-m', strtotime('-32 day'));
        }

        $where_total_price[]  = ['a.financial_status', 'in', ["partially_paid", "paid", "partially_refunded", "refunded"]];

        //汇率
        $order_exchange = Db::name('order_exchange')
            ->where(['date' => $date])
            ->find();
        
        $lists = Db::name('order_source')
            ->field('
                a.order_id,
                a.name,
                a.financial_status,
                a.fulfillment_status,
                a.created_at,
                a.status,
                a.tag_source,
                a.tag_campaign,
                b.freight,
                b.material,
                b.dispute,
                b.total_price,
                b.refund,
                a.landing_site,
                a.tags,
                b.sku,
                b.estimate_freight,
                b.estimate_weight,
                b.freight_status,
                b.material_status,
                b.disputeapi_type,
                b.disputeapi_status,
                b.skuquantity,
                b.currency
            ')
            ->alias('a')
            ->join('qu_order_achievement b','b.order_id = a.order_id', 'left')
            ->where($where)
            ->where($where_total_price)
            ->select()
            ->toArray();
            
        foreach ($lists as $k=>$v) {
            //填充预估物流费
            if($v['freight'] <= 0.001){
                $v['freight'] = $v['estimate_freight'];
            }
            if(empty($v['fulfillment_status'])){
                $lists[$k]['fulfillment_status'] = "未发货";
            }
            $lists[$k]['tag_source'] = $this->tag_source($v['tag_source']);
            $lists[$k]['tag_campaign'] = $this->tag_campaign($v['tag_campaign']);
            //物流--人民币转美金
            $lists[$k]['freight'] = (float)number_format((float)$v['freight'] / $order_exchange['dollar'], 3, '.', '');
            //采购
            $lists[$k]['material'] = (float)number_format((float)$v['material'] / $order_exchange['dollar'], 3, '.', '');
            //欧元换成美金
            if($v['currency'] == "EUR"){
                $lists[$k]['total_price'] = (float)number_format((float)$v['total_price'] * $order_exchange['dollar'] / $order_exchange['euro_exchange_rate'], 3, '.', '');
            }
            $lists[$k]['dollar'] = $order_exchange['dollar'];
            $lists[$k]['euro_exchange_rate'] = $order_exchange['euro_exchange_rate'];
        }
        
        $fileName = "export_" . $tableName . "_" . time();
        return Excel::exportData($lists, $header, $fileName, 'xlsx');
    }

    /**
    * @desc    编辑
    * @date    2024/7/16
    * @author  zhangheng
    */
    public function edit($data = [])
    {
        if (!$data) {
            return json(DefaultResponse(201, false, '参数不能为空!', null));
        }
        $result = false;

        try {
            
            foreach ($data as $value) {
                $updateData = [
                    'tag_source' => !empty($value["tag_source"]) ? $value["tag_source"] : "other",
                    'tag_campaign' => !empty($value["tag_campaign"]) ? $value["tag_campaign"] : "LabelIsEmpty",
                    'status' => 3,
                ];
                if($updateData['tag_source'] == "直接访问" || $updateData['tag_source'] == "email" || $updateData['tag_source'] == "other"){
                    $updateData['tag_campaign'] = "LabelIsEmpty";
                }
                if($updateData['tag_source'] == "LabelIsEmpty"){
                    $updateData['tag_source'] = "other";
                }
                table('order_source')
                    ->where('id', $value['id'])
                    ->update($updateData);
            }

            $result = true;
            
            if ($result) {
                return json(DefaultResponse(200, false, '更新成功!', null));
            }
            return json(DefaultResponse(201, false, '更新失败!', null));
        } catch (ValidateException $e) {
            return json(DefaultResponse(201, false, $e->getError(), null));
        } catch (\Exception $e) {
            return json(DefaultResponse(201, false, '更新失败!', null));
        }
        return json(DefaultResponse(200, false, '更新成功!', null));
    }
    
    /**
    * @desc    构建where
    * @date    2024/7/16
    * @author  zhangheng
    */
    public function buildTableParames($params)
    {
        $keyword = $params['keyword'] ?? "";
        $createtime = $params['createtime'] ?? "";
        $order_id = $params['order_id'] ?? "";
        $sku = $params['sku'] ?? "";
        $tag_campaign = $params['tag_campaign'] ?? "";
        $status = $params['status'] ?? "";
        $tag_source = $params['tag_source'] ?? "";
        $shop_id = $params['shop_id'] ?? "";

        $where = [];
        if (!empty($shop_id)) {
            $where[] = array('shop_id', '=', $shop_id);
        }

        if (!empty($order_id)) {
            $order_ids = explode("\n", trim($order_id));
            $order_ids_new = array_map('trim', array_filter($order_ids, function($v) {  
                return !empty(trim($v));  
            }));
            if (!empty($order_ids_new)) {
                $where[] = ['order_id|name|order_number', 'in', $order_ids_new];
            }
        }

        if (!empty($sku)) {
            $where[] = array('sku', 'LIKE', '%' . trim($sku) . '%');
        }
        if (!empty($tag_campaign)) {
            $where[] = array('tag_campaign', '=', $tag_campaign);
        }
        if (!empty($tag_source)) {
            $where[] = array('tag_source', '=', $tag_source);
        }
        if (!empty($keyword)) {
            $keyword = '%' . $keyword . '%';
            $where[] = ['
                order_number|
                first_source|
                first_campaign|
                last_source|
                last_campaign|
                firstVisit_campaign|
                firstVisit_source|
                lastVisit_campaign|
                lastVisit_source|
                landing_site', 'like', $keyword
            ];
        }

        if (!empty($status)) {
            switch ($status) {
                case 'matched':
                    $where[] = array('status', '=',1);
                    break;
                case 'matching_failed':
                    $where[] = array('status', '=',2);
                    break;
                case 'manually_modifying':
                    $where[] = array('status', '=',3);
                    break;
                case 'unmatched':
                    $where[] = array('status', '=',0);
                    break;
                case 'fb':
                    $where[] = array('tag_source', '=', 'fb');
                    break;
                case 'tt':
                    $where[] = array('tag_source', '=', 'tt');
                    break;
                case 'ggad':
                    $where[] = array('tag_source', '=', 'ggad');
                    break;
                case 'email':
                    $where[] = array('tag_source', '=', 'email');
                    break;
                case 'other':
                    $where[] = array('tag_source', '=', 'other');
                    break;
                case 'direct_access':
                    $where[] = array('tag_source', '=', '直接访问');
                    break;
                case '待确认':
                    $where[] = array('tag_source', '=', '待确认');
                    break;
                case 'AllianceInternetCelebrity':
                    $where[] = array('tag_source', '=', 'impact');
                    break;
                default:
            }
        }

        if (!empty($createtime)) {
            $where[] = ['createtime', '>=', strtotime($createtime['0'])];
            $where[] = ['createtime', '<=', strtotime($createtime['1']) + 3600*24-1];
        }

        if (empty($where)) {
            // $yesterday = strtotime(date('Y-m-d', strtotime('-1 day') - timezone())); 
            // $where[] = ['createtime', '>=', $yesterday];
            // $where[] = ['createtime', '<=', $yesterday + 3600*24-1];
        }

        return $where;
    }
    
    /**
    * @desc    按渠道查看销售业绩
    * @date    2024/7/22
    * @author  zhangheng
    */
    public function SalesPerformanceChannel($params = [])
    {
        $where = $this->buildTableParames($params);
        
        $where_total_price[]  = ['financial_status', 'in', ["partially_paid", "paid", "partially_refunded", "refunded"]];

        $lists = Db::name('order_source')
            ->field('COUNT(id) as count,sum(total_price) as total_price,tag_source,tag_campaign')
            ->where($where)
            ->where($where_total_price)
            ->group('tag_source,tag_campaign')
            ->order('tag_source desc,tag_campaign desc')
            ->select()
            ->toArray();
        $lists_news = array();
        if(!empty($lists)){
            $groupedStatistics = [];
            foreach ($lists as $item) {
                $tagSource = $item["tag_source"];
                $totalPrice = $item["total_price"];
                $totalCount = $item["count"];
                if (!empty($tagSource) && is_numeric($totalPrice)) {
                    if (!array_key_exists($tagSource, $groupedStatistics)) {
                        $groupedStatistics[$tagSource] = ["total_price_sum" => 0,"count" => 0, "children" => []];
                    }
                    $groupedStatistics[$tagSource]["total_price_sum"] += $totalPrice;
                    $groupedStatistics[$tagSource]["count"] += $totalCount;
                    if (!in_array($tagSource, $groupedStatistics[$tagSource]["children"])) {
                        $groupedStatistics[$tagSource]["children"][] = $item;
                    }
                }
            }
            //统计
            foreach ($groupedStatistics as $k => $v) {
                $lists_new = array(
                    'total_price' => number_format((float)$v['total_price_sum']*1, 2, '.', ''),
                    'count' => $v['count'],
                    'tag_source' => $k,
                    'tag_campaign' => "统计",
                    'children' => $v['children'],
                );
                $lists_news[] = $lists_new;
            }
        }
        return json(DefaultResponse(200, true, '获取成功', $lists_news));
    }

    /**
    * @desc    按sku查看销售业绩
    * @date    2024/7/22
    * @author  zhangheng
    */
    public function SalesPerformanceSku($params = [])
    {
        $where = $this->buildTableParames($params);

        $where_total_price[]  = ['financial_status', 'in', ["partially_paid", "paid", "partially_refunded", "refunded"]];

        $lists = Db::name('order_source')
            ->field('total_price,tag_campaign,skus')
            ->where($where)
            ->where($where_total_price)
            ->order('tag_campaign desc')
            ->select()
            ->toArray();
            
        $lists_news = array();
        if(!empty($lists)){
            $groupedStatistics = [];
            foreach ($lists as $item) {
                $tagCampaign = $item["tag_campaign"];
                $totalPrice = $item["total_price"];
                if (!empty($tagCampaign) && is_numeric($totalPrice)) {
                    if (!array_key_exists($tagCampaign, $groupedStatistics)) {
                        $groupedStatistics[$tagCampaign] = ["total_price_sum" => 0, "children" => []];
                    }
                    $groupedStatistics[$tagCampaign]["total_price_sum"] += $totalPrice;
                    if (!in_array($tagCampaign, $groupedStatistics[$tagCampaign]["children"])) {
                        $groupedStatistics[$tagCampaign]["children"][] = $item['skus'];
                    }
                }
            }
            //统计
            foreach ($groupedStatistics as $k => $v) {
                $childrens = $this->NumberOfSKUsProcessed($v['children']);
                $lists_new = array(
                    'spu' => "统计",
                    'tag_campaign' => $k,
                    'count' => $childrens['count'],
                    'total_price' => number_format((float)$v['total_price_sum']*1, 2, '.', ''),
                    'children' => $childrens['childrens'],
                );
                $lists_news[] = $lists_new;
            }
        }
        return json(DefaultResponse(200, true, '获取成功', $lists_news));
    }
    /**
    * @desc    按id查询附加信息
    * @date    2024/7/22
    * @author  zhangheng
    */
    public function AdvertisingInquiry($params = [])
    {
        $id = $params['id'] ?? "";
        if (!$id) {
            return json(DefaultResponse(201, false, '参数不能为空!', null));
        }
        //获取Facebook广告系列
        $ApiAdvLogic = new ApiAdvLogic();
        $info = $ApiAdvLogic->GetFacebookAdCampaigns($id);
        $lists_news['name'] = $info['name'] ?? "暂无";
        return json(DefaultResponse(200, true, '获取成功', $lists_news));
    }
    
    /**
    * @desc    处理sku件数
    * @date    2024/7/16
    * @author  zhangheng
    */
    public function NumberOfSKUsProcessed($children)
    {
        $groupedStatistics = [];
        foreach ($children as $item) {  
            // 使用explode来分割字符串，处理可能包含多个商品的情况  
            $items = explode(',', $item);  
            foreach ($items as $subItem) {  
                if (strpos($subItem, '-') !== false) {  
                    $spus = explode('-', $subItem);
                    $spu = $spus[0];
                    if(isset($groupedStatistics[$spu])){
                        $groupedStatistics[$spu] += 1;
                    }else{
                        $groupedStatistics[$spu] = 1;
                    }
                    if (strpos($subItem, '+') !== false) {  
                        //查询有几个加号
                        $plusCount = substr_count($subItem, '+'); 
                        $groupedStatistics[$spu] += (int)$plusCount;
                    }
                }
            }  
        }
        $childrens = [];  
        $total_count = 0; 
        foreach ($groupedStatistics as $k => $v) {  
            $new_arr = [  
                'spu' => $k,  
                'count' => $v  
            ];  
            $childrens[] = $new_arr;  
            $total_count += $v;  
        }
        $return = [  
            'childrens' => $childrens,  
            'count' => $total_count  
        ]; 
        return $return;
    }
    

    /**
    * @desc    识别成中文运营名称
    * @date    2024/7/31
    * @author  zhangheng
    */
    public function tag_source($tag_source)
    {
        $ChannelLabelList = $this->arr_source();
        foreach ($ChannelLabelList as $k=>$v) {
            if($v['value'] == $tag_source){
                return $v['label'];
            }
        }
        return $tag_source;
    }

    /**
    * @desc    识别成中文运营名称
    * @date    2024/7/31
    * @author  zhangheng
    */
    public function tag_campaign($tag_campaign)
    {
        $OperationLabelList = $this->arr_campaign();
        foreach ($OperationLabelList as $k=>$v) {
            if($v['value'] == $tag_campaign){
                return $v['label'];
            }
        }
        return $tag_campaign;
    }

    /**
    * @desc    arr_campaign
    * @date    2024/7/16
    * @author  zhangheng
    */
    public function arr_source()
    {
        $data = [  
            ['value' => 'fb', 'label' => 'Facebook'],  
            ['value' => 'tt', 'label' => 'TikTok'],  
            ['value' => 'ggad', 'label' => 'google'],  
            ['value' => 'LabelIsEmpty', 'label' => '渠道标签为空'],
            ['value' => '直接访问', 'label' => '直接访问'], 
            ['value' => '待确认', 'label' => '待确认'],   
            ['value' => 'email', 'label' => 'email'],  
            ['value' => 'impact', 'label' => 'impact'],  
            ['value' => 'other', 'label' => 'other']
        ];
        return $data;
    }
    /**
    * @desc    arr_campaign
    * @date    2024/7/16
    * @author  zhangheng
    */
    public function arr_campaign()
    {
        $data = [  
            // ['value' => 'wwj', 'label' => '伍文娟'],  
            // ['value' => 'hq', 'label' => '胡倩'],  
            // ['value' => 'zb', 'label' => '曾贝'],  
            // ['value' => 'ly', 'label' => '龙颖'],  
            // ['value' => 'hj', 'label' => '胡锦'],  
            ['value' => 'wd', 'label' => '王丹'],  
            ['value' => 'ry', 'label' => '任莹'],  
            ['value' => 'wyl', 'label' => '吴玉龙'],  
            ['value' => 'lxy', 'label' => '李笑妍'],  
            ['value' => 'dqh', 'label' => '丁秋涵'],  
            ['value' => 'gjl', 'label' => '郭纪伦'],  
            ['value' => 'ljx', 'label' => '李佳娴'],  
            ['value' => 'zcc', 'label' => '张聪聪'], 
            ['value' => 'wb', 'label' => '魏冰'],   
            ['value' => 'lh', 'label' => '刘慧'],   
            ['value' => 'LabelIsEmpty', 'label' => '运营标签为空'],  
        ];
        return $data;
    }
    

}
